package adminorg;

//import com.vaadin.data.*;
//import com.vaadin.data.util.*;
import com.oros.db.*;

import java.io.*;
import java.util.*;

import com.oros.db.AEFilter.FilterOperator;
import com.vaadin.data.util.filter.Compare.Equal;
import com.vaadin.data.*;
import com.vaadin.Application;
import de.rac.vaadin.framework.ui.*;
public class DatabaseHelper implements Serializable,DatabasePool {
	  /**
	 */ 
	private static final String[] superAdminEmails = new String[]
			{
		      "stepan@oros.kiev.ua","vbanyas@gmail.com","vdobryvechir@gmail.com","bv@oros.kiev.ua","alex@visotsky.org"
			};
	public DocumentView views;
	private boolean isSuperadmin=false;
	public String currentUser=null;
	private boolean currentUserSaveReady=false;
	private static final long serialVersionUID = 7867686877681L;
	private AETableContainer CompContainer=null;
	  private Integer companyId=null;
	  private AEVaadTable engine = new com.oros.db.appengine.GAEVaadEngine();
	  private AETableContainer CompAccContainer=null;
	  private AETableContainer OrgschemaContainer = null;
	  private AETableContainer OrgschemaReadContainer = null;
	  private AETableContainer DocumentContainer = null;
	  private AETableContainer DocRightContainer = null;
	  private AETableContainer CurrentUserParamContainer = null;
	  private AETableContainer UserTrackContainer = null;
	  private AETableContainer StatisticDataContainer = null;
	  private AETableContainer BPPriorityContainer = null;
	  private AETableContainer BPTaskTypeContainer = null;
	  private AETableContainer BPEventContainer = null;
	  private AETableContainer BPObjectContainer = null;
	  private AETableContainer BPTaskContainer = null;
	  private AETableContainer BPPostContainer = null;
	  private AETableContainer BPDocumentContainer = null;
	  private AETableContainer BPEventPostContainer = null;
	  private AETableContainer BPEventTaskContainer = null;
	  private AETableContainer PanelsContainer = null;
	  private AETableContainer ItemPanelsContainer = null;
	  private ArrayList<Property.ValueChangeListener> companyListeners=
			  new ArrayList<Property.ValueChangeListener>();
	  private static AETableDefinition CompAccTable=new AETableDefinition("compacc",
			   new String[]{"id","company","email","nik","name","isadmin","isstatadmin","isstatuser","ispanel","isprocessadmin","isprocessuser","isfinuser","istrainingadmin"},
			   new Object[]{null,null,"","","",false,false,false,false,false,false,false,false},
			   new Class[]{Integer.class,Integer.class,String.class,String.class,String.class,Boolean.class,Boolean.class,Boolean.class,Boolean.class,Boolean.class,Boolean.class,Boolean.class,Boolean.class},"id","company");
      private static AETableDefinition UserTrackTable=new AETableDefinition("users",
    		   new String[]{"id","email","auth","last_company","last_page"},
    		   new Object[]{null,"","",0,""},
    		   new Class[]{Integer.class,String.class,String.class,Integer.class,String.class},
    		   "id",null
    		  );
	  private static AETableDefinition CompTable = new AETableDefinition("company",
			   new String[]{"code","name","email","post","id","password"},
			   new Object[]{"","","","",null,""},
			   new Class[]{String.class,String.class,String.class,String.class,Integer.class,String.class},"id",null);
      private static AETableDefinition OrgschemaTable = new AETableDefinition("orgschema",
			   new String[]{"id","pid","company","code","name","dept_name","manager","docs","level","accid"},
			   new Object[]{null,0,null,"","","",false,"",0,null},
			   new Class[]{Integer.class,Integer.class,Integer.class,String.class,String.class,String.class,Boolean.class,String.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition DocumentTable = new AETableDefinition("documents",
			   new String[]{"id","company","post","name_doc","description","file_type","link"},
			   new Object[]{null,null,null,"","","",""},
			   new Class[]{Integer.class,Integer.class,Integer.class,String.class,String.class,String.class,String.class},"id","company");
      private static AETableDefinition DocRightTable = new AETableDefinition("docrights",
			   new String[]{"id","company","docid","post","right"},
			   new Object[]{null,null,null,null,3},
			   new Class[]{Integer.class,Integer.class,Integer.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition StatisticUsesTable = new AETableDefinition("statisticuses",
			   new String[]{"id","company","postid","code","statid","active"},
			   new Object[]{null,null,"null","",null,true},
			   new Class[]{Integer.class,Integer.class,Integer.class,String.class,Integer.class,Boolean.class},"id","company");
      private static AETableDefinition OrgLevelTable = new AETableDefinition("orglevel",
			   new String[]{"id","company","level","name"},
			   new Object[]{null,null,0,""},
			   new Class[]{Integer.class,Integer.class,Integer.class,String.class},"id","company");
      private static AETableDefinition APeriodPropsTable = new AETableDefinition("aperiodprops",
			   new String[]{"id","company","type","code","start_day","begdate","periodview","statquant","current","closedate"},
			   new Object[]{null,null,"WEEKLY","","WEDNESDAY",null,true,12,"TODAY",null},
			   new Class[]{Integer.class,Integer.class,String.class,String.class,String.class,Date.class,Boolean.class,Integer.class,String.class,Date.class},"id","company");
      private static AETableDefinition DictStatisticTable = new AETableDefinition("dictstatistic",
			   new String[]{"id","company","code","name","description","docs","aperiod","dooble","stat1","stat2","calc","formula"},
			   new Object[]{null,null,"","","","",null,false,null,null,false,""},
			   new Class[]{Integer.class,Integer.class,String.class,String.class,String.class,String.class,Integer.class,
			                  	Boolean.class,Integer.class,Integer.class,Boolean.class,String.class},"id","company");
      private static AETableDefinition StatisticDataTable = new AETableDefinition("statisticdata",
			   new String[]{"id","company","statisticuses","date","sum"},
			   new Object[]{null,null,null,null,(double)0},
			   new Class[]{Integer.class,Integer.class,Integer.class,Date.class,Double.class},"id","company");
	  private static AETableDefinition BPPriorityTable = new AETableDefinition("bppriority",
			   new String[]{"id","company","code","name","level"},
			   new Object[]{null,null,"","",0},
			   new Class[]{Integer.class,Integer.class,String.class,String.class,Integer.class},"id","company");
	  private static AETableDefinition BPTaskTypeTable = new AETableDefinition("bptasktype",
			   new String[]{"id","company","kind","priority"},
			   new Object[]{null,null,0,0},
			   new Class[]{Integer.class,Integer.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition BPEventTable = new AETableDefinition("bpevent",
			   new String[]{"id","company","code","name","initiation","period","calendar","daily","weekly","monthly"},
			   new Object[]{null,null,"","",0,new java.util.Date(0),0,new java.util.Date(0),1,1},
			   new Class[]{Integer.class,Integer.class,String.class,String.class,Integer.class,
			                  	java.util.Date.class,Integer.class,java.util.Date.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition BPObjectTable = new AETableDefinition("bpobject",
			   new String[]{"id","company","code","name","description","event","post"},
			   new Object[]{null,null,"","","",null,null},
			   new Class[]{Integer.class,Integer.class,String.class,String.class,String.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition BPTaskTable = new AETableDefinition("bptask",
			   new String[]{"id","company","bp","code","active","name","description","time","deadline","priority","tasktype"},
			   new Object[]{null,null,0,"",true,"","","","",0,0},
			   new Class[]{Integer.class,Integer.class,Integer.class,String.class,Boolean.class,String.class,String.class,Integer.class,java.util.Date.class,
			                  	Integer.class,Integer.class},"id","company");
      private static AETableDefinition BPPostTable = new AETableDefinition("bppost",
			   new String[]{"id","company","code","bp","task","post","verifying"},
			   new Object[]{null,null,"",0,0,0,false},
			   new Class[]{Integer.class,Integer.class,String.class,Integer.class,
			                  	Integer.class,Integer.class,Boolean.class},"id","company");
      private static AETableDefinition BPDocumentTable = new AETableDefinition("bpdocument",
			   new String[]{"id","company","bp","task","docid"},
			   new Object[]{null,null,0,0,0},
			   new Class[]{Integer.class,Integer.class,Integer.class,
			                  	Integer.class,Integer.class},"id","company");
      private static AETableDefinition BPEventPostTable = new AETableDefinition("bpeventpost",
			   new String[]{"id","company","eventtask","post"},
			   new Object[]{null,null,0,0},
			   new Class[]{Integer.class,Integer.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition BPEventTaskTable = new AETableDefinition("bpeventtask",
			   new String[]{"id","company","event","task"},
			   new Object[]{null,null,0,0},
			   new Class[]{Integer.class,Integer.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition PanelsTable = new AETableDefinition("panels",
			   new String[]{"id","company","account","name"},
			   new Object[]{null,null,"",""},
			   new Class[]{Integer.class,Integer.class,String.class,String.class},"id","company");
      private static AETableDefinition ItemPanelsTable = new AETableDefinition("itempanels",
			   new String[]{"id","company","panels","statisticusesid","xpos","xpos","width","height"},
			   new Object[]{null,null,null,null,null,null,null,null},
			   new Class[]{Integer.class,Integer.class,Integer.class,Integer.class,Integer.class,Integer.class,Integer.class,Integer.class},"id","company");
      private static AETableDefinition MinMaxStatTable = new AETableDefinition("minmaxstat",
			   new String[]{"id","company","last_date","minval","maxval"},
			   new Object[]{null,null,null,null,null},
			   new Class[]{Integer.class,Integer.class,Date.class,Double.class,Double.class},"id","company"); 
      private static AETableDefinition OrgschemaReadOnlyTable;
      static
      {
    	  AETableDefinition d =(AETableDefinition)OrgschemaTable.clone();
    	  d.addGeneratedColumns(
    	  new AEGeneratedColumn[] { //generated columns: (null if not used)
					//we need 2 columns: post_name, right_title
					// 1. post name = orgschema.name + orgschema.dept_name
					new AEGeneratedColumn("post_name",String.class,"",
							new AEGeneratedData(){
								private static final long serialVersionUID = 9001216881294403823L;
								@Override
								public Object generateCell(
										AETableContainer table,
										AERawItem item,int columnNo,int action) {
									if (item.getItemProperty("orgschema.name")==null)
										return item.getItemProperty("name").getValue()+" ("
										+item.getItemProperty("dept_name").getValue()+")";
									return item.getItemProperty("orgschema.name").getValue()+" ("
											+item.getItemProperty("orgschema.dept_name").getValue()+")";
								}
						
					})
    	  });
    	  OrgschemaReadOnlyTable = d;
      }
    private Application application;
    private ResourcePool resource;
	public DatabaseHelper(Application application)
	{
		this.application = application;
		views = new DocumentView(this);
	}
	
	public Application getApplication() {return application; }
	public boolean isUserSuperAdmin() { return isSuperadmin;}
	public void setNewUser(String user) throws Exception
	{
		currentUserSaveReady=false;
		if (currentUser==null || !currentUser.equals(user))
		{
			currentUser=user;
		}
	}
	public void setSuperAdmin(boolean isSuperadmin)
	{
		this.isSuperadmin = isSuperadmin;
		if (isSuperadmin) anyCompanyPermission=null;
	}
	public boolean isSuperAdminEmail(String email)
	{
		int n=superAdminEmails.length;
		for(int i=0;i<n;i++) if (superAdminEmails[i].equals(email)) return true;
		return false;
	}
	public void setResources(String resourcePath)
	{
		resource = new ResourcePool(application.getLocale(),application,null,null,resourcePath,null);
		resource.setDatabasePool(this);
	}
	public ResourcePool getResource() { return resource; }
	public String getWords(String word)
	{
		String res = resource.getTranslationSoft(word);
		if (res==null) return word; else
		return res;
	}
	public String[] getTranslatedStrings(String[] src)
	{
		if (src==null) return null;
		int n = src.length;
		String[] dst=new String[n];
		for(int i=0;i<n;i++)
			dst[i]=getWords(src[i]);
		return dst;
	}
	public void resetCompanyListeners()
	{
		companyListeners.clear();
	}
	public void addCompanyListener(Property.ValueChangeListener listener)
	{
		companyListeners.add(listener);
	}
	public Integer getCompanyId() { return companyId; }
	public void setCompanyId(Object companyId) throws Exception
	{
		if (companyId==null) return;
		if (!(companyId instanceof Integer))
		{
			companyId = Integer.parseInt(companyId.toString());
		}
		if (this.companyId==null || !this.companyId.equals(companyId))
		{
            this.companyId = (Integer)companyId;
            
            if (CompAccContainer != null) CompAccContainer.setTableParentNewValue(companyId);
            if (OrgschemaContainer != null) OrgschemaContainer.setTableParentNewValue(companyId);
            if (OrgschemaReadContainer != null) OrgschemaReadContainer.setTableParentNewValue(companyId);
            if (DocumentContainer!= null) DocumentContainer.setTableParentNewValue(companyId);
            if (DocRightContainer!= null) DocRightContainer.setTableParentNewValue(companyId);
            if (CurrentUserParamContainer!= null) 
            	{
            	
            	   CurrentUserParamContainer.setTableParentNewValue(companyId);
            	   
            	}
            if (OrgLevelContainer!= null) OrgLevelContainer.setTableParentNewValue(companyId);
            if (APeriodPropsContainer!= null) APeriodPropsContainer.setTableParentNewValue(companyId);
            if (DictStatisticContainer!= null) DictStatisticContainer.setTableParentNewValue(companyId);
            if (StatisticUsesContainer!= null) StatisticUsesContainer.setTableParentNewValue(companyId);
            if (StatisticDataContainer!= null) StatisticDataContainer.setTableParentNewValue(companyId);
			if (BPPriorityContainer != null) BPPriorityContainer.setTableParentNewValue(companyId);
            if (BPTaskTypeContainer != null) BPTaskTypeContainer.setTableParentNewValue(companyId); 
            if (BPObjectContainer != null) BPObjectContainer.setTableParentNewValue(companyId); 
            if (BPTaskContainer != null) BPTaskContainer.setTableParentNewValue(companyId); 
            if (BPPostContainer != null) BPPostContainer.setTableParentNewValue(companyId); 
            if (BPDocumentContainer != null) BPDocumentContainer.setTableParentNewValue(companyId); 
            if (BPEventPostContainer != null) BPEventPostContainer.setTableParentNewValue(companyId); 
            if (BPEventTaskContainer != null) BPEventTaskContainer.setTableParentNewValue(companyId); 
            if (PanelsContainer!=null) PanelsContainer.setTableParentNewValue(companyId);
            if (ItemPanelsContainer!=null) ItemPanelsContainer.setTableParentNewValue(companyId);
            for(Property.ValueChangeListener listener:companyListeners)
            {
            	listener.valueChange(null);
            }
            if (currentUser!=null && currentUser.length()>0 && currentUserSaveReady)
            {
               getCurrentUserTrackItem().getItemProperty("last_company").setValue(companyId);
               saveCurrentUserTrackInfo();
            }
		}
	}
 
    public AETableContainer getCompContainer() throws Exception
    {
       if (CompContainer==null)
       {
    	   CompContainer=new AETableContainer(CompTable,true,engine);
    	   /*********************************
   		   if (CompContainer.size()==0)
   		   {
   			   CompContainer.addNewItem(new Object[]{"ost","OST Ltd.","stepan@oros.kiev.ua","Директор",null});
   			   CompContainer.addNewItem(new Object[]{"itl","Intensive Technologies Ltd.","dobrivecher@yahoo.com","Программист",null});
   			   CompContainer.commit();
   		   }
   		   ********************************/
   		   CompContainer.sort(new String[]{"name"},new boolean[]{true});
   
   		   //CompContainer.showSourceDetailedInfo(AETableContainer.SHOW_INFO_FILTERED | AETableContainer.SHOW_INFO_WITH_TYPES);
   		   //CompContainer.addContainerFilter(new com.vaadin.data.util.filter.Compare.Equal("email","dobrivecher@yahoo.com"));
        }
       return CompContainer;
    }
    public AETableContainer getCompAccContainer() throws Exception
    {
       if (CompAccContainer==null)
       {
    	   if (companyId==null) throw new Exception("Company is not set");
    	   CompAccContainer=new AETableContainer(CompAccTable,false,engine);
    	   CompAccContainer.setTableParentColumn("company",companyId);
    	   CompAccContainer.refresh();
    	   CompAccContainer.addForeignKeyConstraint(getOrgschemaReadContainer(), "accid");
        }
       return CompAccContainer;
    }
    
    public AETableContainer getCurrentUserParamContainer()  throws Exception{
    	if (CurrentUserParamContainer==null){
    		 if (companyId==null) throw new Exception("Company is not set");
    		 CurrentUserParamContainer=new AETableContainer(CompAccTable,false,engine);
    		 CurrentUserParamContainer.setTableParentNewValue(companyId);
    	}
    	CurrentUserParamContainer.removeAllContainerFilters();
    	//CurrentUserParamContainer.AppEngineAddFilter("email", FilterOperator.EQUAL, currentUser);
    	CurrentUserParamContainer.addContainerFilter(new EqualToUserFilter("email",this));
    	CurrentUserParamContainer.refresh();
		return CurrentUserParamContainer;    	
    }
	private LinkedHashMap<String,Boolean> anyCompanyPermission;
    private void addCompAccPermission(AETableContainer container,String columnName)
    {
    	boolean hasPermission=false;
    	HashSet<?> vals = container.getDistinctColumnValues(columnName);
    	for(Object val:vals)
    	{
    		if (val instanceof Boolean && ((Boolean)val).booleanValue()) { hasPermission=true;break;}
    	}
    	anyCompanyPermission.put(columnName, new Boolean(hasPermission));
    }
    
	public boolean setCompUserContainer(String email) throws Exception
    {
    	   AETableContainer CompAccUserContainer=new AETableContainer(CompAccTable,false,engine);
    	   //CompAccUserContainer.refresh();
    	   //System.out.println("setCompUserContainer: ["+email+"]");
    	   //CompAccUserContainer.showSourceDetailedInfo(AETableContainer.SHOW_INFO_FULL_ROW);
    	   CompAccUserContainer.AppEngineAddFilter("email", FilterOperator.EQUAL, email);
    	   CompAccUserContainer.refresh();
    	   //CompAccUserContainer.showSourceDetailedInfo(AETableContainer.SHOW_INFO_FULL_ROW);
    	   //CompAccUserContainer.showSourceDetailedInfo(AETableContainer.SHOW_INFO_FILTERED | AETableContainer.SHOW_INFO_WITH_TYPES);
    	   HashSet<?> comps = CompAccUserContainer.getDistinctColumnValues("company");
           if (comps.size()==0) 
        	   {
        	      System.out.println("Not found this user:"+email);
        	      return false;
        	   }
		   anyCompanyPermission = new LinkedHashMap<String,Boolean>();
    	   Collection<?> columns = CompAccUserContainer.getContainerPropertyIds();
    	   for(Object column:columns)
    	   {
    		   String columnName=(String)column;
    		   Class<?> tp = CompAccUserContainer.getType(columnName);
               if (tp==Boolean.class) addCompAccPermission(CompAccUserContainer,columnName);     		   
    	   }	
           if ((companyId==null)||(!comps.contains(companyId)))
           {
        	  for(Object comp:comps)
        	  {
        		  //we select the first company in the container
        		  //suggestion for the future:
        		  //we can keep records for this user what is his last used company 
        		  setCompanyId(comp);
        		  break;
        	  }
           }
           getCompContainer().removeAllContainerFilters();
           getCompContainer().addContainerFilter(new AEInFilter("id",comps));
           return true;
    }
	public boolean isCompAccPropertySetForAnyCompany(String columnName)
	{
		if (anyCompanyPermission==null) return true;
		Boolean vl = anyCompanyPermission.get(columnName);
		if (vl==null || !vl.booleanValue()) return false;
		return true;
	}
	
	public boolean getReadAllowed(String CompAcc_AuthColumn) {
		boolean isReadAllowed;
		if (CompAcc_AuthColumn!=null && CompAcc_AuthColumn.length()>0)
		{
			isReadAllowed=false;
			try
			{
				AETableContainer aContainer = getCurrentUserParamContainer();
				//System.out.println("Verified rights for "+sourceData.getViewTable()+" with "+aContainer.size());
				HashSet<?> dat=aContainer.getDistinctColumnValues(CompAcc_AuthColumn);
				for(Object d:dat)
				{
					if (d instanceof Boolean) if  (((Boolean)d).booleanValue())
                    {
                       isReadAllowed=true;
                       break;
                    }
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		else isReadAllowed=true;
		return isReadAllowed;
	}
	
    public void doc_orgschema_test() throws Exception
    {
    	 OrgschemaContainer.addNewItem(new String[]{"accid","pid","name","dept_name"}
    	     ,new Object[]{1,1,"1.1.teacher","school 1.1."+companyId});
    	 OrgschemaContainer.addNewItem(new String[]{"accid","pid","name","dept_name"}
	     ,new Object[]{1,2,"1.2.director","firma 1.2."+companyId});
    	 OrgschemaContainer.addNewItem(new String[]{"accid","pid","name","dept_name"}
	     ,new Object[]{1,3,"1.3.coach","stadium 1.3."+companyId});
    	 OrgschemaContainer.addNewItem(new String[]{"accid","pid","name","dept_name"}
	     ,new Object[]{2,1,"2.1.geschaftsfuehrer","Deutschland 2.1."+companyId});
    	 OrgschemaContainer.addNewItem(new String[]{"accid","pid","name","dept_name"}
	     ,new Object[]{2,2,"2.2.programmer","Metro 2.2."+companyId});
    	 OrgschemaContainer.addNewItem(new String[]{"accid","pid","name","dept_name"}
	     ,new Object[]{3,1,"3.1.squirrel","forest 3.1."+companyId});
    	 OrgschemaContainer.addNewItem(new String[]{"accid","pid","name","dept_name"}
	     ,new Object[]{4,1,"4.1.programmer","railroad station 4.1."+companyId});
    	 OrgschemaContainer.commit();
    }
    public AETableContainer getOrgschemaContainer() throws Exception
    {
       if (OrgschemaContainer==null)
       {
    	   if (companyId==null) throw new Exception("Company is not set");
    	   OrgschemaContainer=new AETableContainer(OrgschemaTable,false,engine);
    	   OrgschemaContainer.setTableParentNewValue(companyId);
    	   OrgschemaContainer.refresh();
    	   OrgschemaContainer.addForeignKeyConstraint(getOrgschemaReadContainer(), "pid");
    	   OrgschemaContainer.addForeignKeyConstraint(getStatisticUsesContainer(), "postid");
    	   if (OrgschemaContainer.size()<2) 
    		   doc_orgschema_test();
       	   if (OrgschemaReadContainer!=null)
    	   {
              AEContainerToContainerListener.setContainerToContainerListening(
                   OrgschemaContainer,OrgschemaReadContainer, 
                   AEContainerToContainerListener.SECOND_LISTENS_FIRST);    		   
    	   }
        }
       return OrgschemaContainer;
    }
    public AETableContainer getOrgschemaReadContainer() throws Exception
    {
       if (OrgschemaReadContainer==null)
       {
    	   if (companyId==null) throw new Exception("Company is not set");
    	   OrgschemaReadContainer=new AETableContainer(OrgschemaReadOnlyTable,false,engine);
    	   OrgschemaReadContainer.setTableParentNewValue(companyId);
    	   if (OrgschemaContainer!=null)
    	   {
              AEContainerToContainerListener.setContainerToContainerListening(
                   OrgschemaContainer,OrgschemaReadContainer, 
                   AEContainerToContainerListener.SECOND_LISTENS_FIRST);    		   
    	   }
        }
       return OrgschemaReadContainer;
    }
    public AETableContainer getDocumentContainer() throws Exception
    {
       if (DocumentContainer==null)
       {
    	   if (companyId==null) throw new Exception("Company is not set");
    	   DocumentContainer=new AETableContainer(DocumentTable,false,engine);
    	   DocumentContainer.setTableParentNewValue(companyId);
   		   /****************
    	   if (DocumentContainer.size()==0)
   		   {
   			  DocumentContainer.addCustomItem(new Object[]{});
   		   }
   		   *******************/
        }
       return DocumentContainer;
    }
    public AETableContainer getDocRightContainer() throws Exception
    {
        if (DocRightContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   DocRightContainer=new AETableContainer(DocRightTable,false,engine);
     	   DocRightContainer.setTableParentNewValue(companyId);
     	   DocRightContainer.addUniqueIndex(new String[]{"docid","post"});
     	   DocRightContainer.addValidator(new AENotNullIdValidator(new String[]{"docid","post","right"}));
    		   /****************
     	   if (DocumentContainer.size()==0)
    		   {
    			  DocumentContainer.addCustomItem(new Object[]{});
    		   }
    		   *******************/
         }
        return DocRightContainer;
    }
    
    
    /***************************** 
    DICTIONARY LEVEL
    ******************************/
    private AETableContainer OrgLevelContainer = null;
    public AETableContainer getOrgLevelContainer() throws Exception{
    	if (OrgLevelContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	  OrgLevelContainer=new AETableContainer(OrgLevelTable,false,engine);
     	  OrgLevelContainer.setTableParentNewValue(companyId);
     	  OrgLevelContainer.addUniqueIndex(new String[]{"level"});
     	  OrgLevelContainer.addUniqueIndex(new String[]{"name"});
     	  OrgLevelContainer.refresh();
     	  OrgLevelContainer.addForeignKeyConstraint(getOrgschemaReadContainer(), "level");
     	  //OrgLevelContainer.addValidator(new AENotNullIdValidator(new String[]{"level","name"}));
    		   /****************
     	   if (OrgLevelContainer.size()==0)
    		   {
    			  OrgLevelContainer.addCustomItem(new Object[]{});
    		   }
    		   *******************/
         }
        return OrgLevelContainer;
    } 
    
    public String getLevelByNumb(int Numb) throws Exception{
    	this.getOrgLevelContainer().removeAllContainerFilters();
    	this.getOrgLevelContainer().addContainerFilter(new Equal("level",Numb));
    	Object ID = OrgLevelContainer.firstItemId();
    	if (ID==null) return null;
    	else {
    		this.getOrgLevelContainer().removeAllContainerFilters();
    		return	this.getOrgLevelContainer().getContainerProperty(ID, "name").getValue().toString();
    	}    	
    }
    /****************************
     * User tracking table
     ***************************/
    public AETableContainer getUserTrackContainer(String email) throws Exception 
    {
        {
        	UserTrackContainer=new AETableContainer(UserTrackTable,false,engine);
     	    UserTrackContainer.AppEngineAddFilter("email", FilterOperator.EQUAL, email);
        	UserTrackContainer.refresh();
        	if (UserTrackContainer.size()==0)
        	{
        		UserTrackContainer.addNewItem(new String[]{"email"}, new Object[]{email});
        		UserTrackContainer.commit();
        	}
        	currentUserSaveReady=true;
        }
        return UserTrackContainer;
    }
    public AERawItem getCurrentUserTrackItem() { return (AERawItem)UserTrackContainer.getItem(UserTrackContainer.firstItemId());}
    public void checkCompanyDocumentList() throws Exception
    {
    	try
    	{
    		views.docservice.getDocumentList();
    	}
    	catch(Exception ignored) {}
    }
    public void saveCurrentUserTrackInfo() throws Exception {
    	  if (currentUserSaveReady) UserTrackContainer.commit();
    	}
    /***************************** 
    DICTIONARY APERIODPROPS
    ******************************/
    private AETableContainer APeriodPropsContainer = null;
    public AETableContainer getAPeriodPropsContainer() throws Exception{
    	if (APeriodPropsContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   APeriodPropsContainer=new AETableContainer(APeriodPropsTable,false,engine);
     	   APeriodPropsContainer.setTableParentNewValue(companyId);
     	   APeriodPropsContainer.addUniqueIndex(new String[]{"type"});
     	   APeriodPropsContainer.addUniqueIndex(new String[]{"code"});
     	   APeriodPropsContainer.refresh();
     	   APeriodPropsContainer.addForeignKeyConstraint(getDictStatisticContainer(), "aperiod");
//     	   APeriodPropsContainer.addValidator(new AENotNullIdValidator(new String[]{"type","code","start_day","begdate","periodview","statquant","current"}));     	   
        }
		return APeriodPropsContainer;
    }
    private static String[] PeriodPropsType = new String[]{"DAILY","WEEKLY","MONTHLY","QUARTERLY"};
    public String[] getPeriodPropsType(){
		return PeriodPropsType;    	
    }
    
    private static String[] StartWeekDay = new String[]{"MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY","SUNDAY"};
    public String[] getStartWeekDay(){
		return StartWeekDay;
	}
    private String[] StartMonthDay = null;
    public String[] getStartMonthDay(){
		if (StartMonthDay==null){
			StartMonthDay = new String[30];
			for(int i=0;i<30;i++) StartMonthDay[i]=Integer.toString(i+1);
		}
    	return StartMonthDay;
	}
    private static String[] CurrentPeriodDay = new String[]{"-1DAY","-2DAY","-3DAY","-4DAY","-5DAY","-1WEEK","-2WEEK","-1MONTH","TODAY"};
    public String[] getCurrentPeriodDay(){
		return CurrentPeriodDay;    	
    }
    
    /***************************** 
    DICTIONARY STATISTIC
    ******************************/
    private AETableContainer DictStatisticContainer = null;
    public AETableContainer getDictStatisticContainer() throws Exception{
    	if (DictStatisticContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	  DictStatisticContainer=new AETableContainer(DictStatisticTable,false,engine);
     	  DictStatisticContainer.setTableParentNewValue(companyId);
     	  DictStatisticContainer.addUniqueIndex(new String[]{"code"});
     	  DictStatisticContainer.addUniqueIndex(new String[]{"name"});
     	  DictStatisticContainer.refresh();
     	  DictStatisticContainer.addForeignKeyConstraint(getStatisticUsesContainer(), "statid");
     	  DictStatisticContainer.addValidator(new AENotNullIdValidator(new String[]{"aperiod"}));
        }
		return DictStatisticContainer;    	
    }
    /***************************** 
    DICTIONARY STATISTIC USES
    ******************************/
    private AETableContainer StatisticUsesContainer = null;
    public AETableContainer getStatisticUsesContainer() throws Exception{
    	if (StatisticUsesContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   StatisticUsesContainer=new AETableContainer(StatisticUsesTable,false,engine);
     	   StatisticUsesContainer.setTableParentNewValue(companyId);
     	   StatisticUsesContainer.addUniqueIndex(new String[]{"postid","statid"});
     	   StatisticUsesContainer.refresh();
     	   StatisticUsesContainer.addValidator(new AENotNullIdValidator(new String[]{"postid","code","statid"}));
        }
		return StatisticUsesContainer;    	
    }
    /***************************** 
    		END DICTIONARY
    ******************************/
	public AETableContainer getBPPriorityContainer() throws Exception{
    	if (BPPriorityContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPPriorityContainer=new AETableContainer(BPPriorityTable,false,engine);
     	   BPPriorityContainer.setTableParentNewValue(companyId);
     	   BPPriorityContainer.addUniqueIndex(new String[]{"code"});
     	   BPPriorityContainer.addUniqueIndex(new String[]{"name"});
     	   //BPPriorityContainer.addValidator(new AENotNullIdValidator(new String[]{"code","name","level"}));
        }
		return BPPriorityContainer;    	
    }
    public AETableContainer getBPTaskTypeContainer() throws Exception{
    	if (BPTaskTypeContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPTaskTypeContainer=new AETableContainer(BPTaskTypeTable,false,engine);
     	   BPTaskTypeContainer.setTableParentNewValue(companyId);
     	   //BPTaskTypeContainer.addUniqueIndex(new String[]{"postid","statid"});
     	   //BPTaskTypeContainer.addValidator(new AENotNullIdValidator(new String[]{"kind","priority","statid"}));
        }
		return BPTaskTypeContainer;    	
    }
    
    public AETableContainer getBPEventContainer() throws Exception{
    	if (BPEventContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPEventContainer=new AETableContainer(BPEventTable,false,engine);
     	   BPEventContainer.setTableParentNewValue(companyId);
     	   //BPEventContainer.addUniqueIndex(new String[]{"postid","statid"});
     	   //BPEventContainer.addValidator(new AENotNullIdValidator(new String[]{"postid","code","statid"}));
        }
		return BPEventContainer;    	
    }
    
    public AETableContainer getBPObjectContainer() throws Exception{
    	if (BPObjectContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPObjectContainer=new AETableContainer(BPObjectTable,false,engine);
     	   BPObjectContainer.setTableParentNewValue(companyId);
     	   BPObjectContainer.addUniqueIndex(new String[]{"code"});
     	   BPObjectContainer.addUniqueIndex(new String[]{"name"});
     	   //BPObjectContainer.addValidator(new AENotNullIdValidator(new String[]{"postid","code","statid"}));
        }
		return BPObjectContainer;    	
    }
    
    public AETableContainer getBPTaskContainer() throws Exception{
    	if (BPTaskContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPTaskContainer=new AETableContainer(BPTaskTable,false,engine);
     	   BPTaskContainer.setTableParentNewValue(companyId);
     	   //BPTaskContainer.addUniqueIndex(new String[]{"postid","statid"});
     	   //BPTaskContainer.addValidator(new AENotNullIdValidator(new String[]{"postid","code","statid"}));
        }
		return BPTaskContainer;    	
    }
    
    public AETableContainer getBPPostContainer() throws Exception{
    	if (BPPostContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPPostContainer=new AETableContainer(BPPostTable,false,engine);
     	   BPPostContainer.setTableParentNewValue(companyId);
     	   //BPPostContainer.addUniqueIndex(new String[]{"postid","statid"});
     	   //BPPostContainer.addValidator(new AENotNullIdValidator(new String[]{"postid","code","statid"}));
        }
		return BPPostContainer;    	
    }
    
    public AETableContainer getBPDocumentContainer() throws Exception{
    	if (BPDocumentContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPDocumentContainer=new AETableContainer(BPDocumentTable,false,engine);
     	   BPDocumentContainer.setTableParentNewValue(companyId);
     	   //BPDocumentContainer.addUniqueIndex(new String[]{"postid","statid"});
     	   //BPDocumentContainer.addValidator(new AENotNullIdValidator(new String[]{"postid","code","statid"}));
        }
		return BPDocumentContainer;    	
    }
    
    public AETableContainer getBPEventPostContainer() throws Exception{
    	if (BPEventPostContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPEventPostContainer=new AETableContainer(BPEventPostTable,false,engine);
     	   BPEventPostContainer.setTableParentNewValue(companyId);
     	   BPEventPostContainer.addUniqueIndex(new String[]{"eventtask","post"});
     	   //BPEventPostContainer.addValidator(new AENotNullIdValidator(new String[]{"postid","code","statid"}));
        }
		return BPEventPostContainer;    	
    }
    
    public AETableContainer getBPEventTaskContainer() throws Exception{
    	if (BPEventTaskContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   BPEventTaskContainer=new AETableContainer(BPEventTaskTable,false,engine);
     	   BPEventTaskContainer.setTableParentNewValue(companyId);
     	   BPEventTaskContainer.addUniqueIndex(new String[]{"event","task"});
     	   BPEventTaskContainer.addValidator(new AENotNullIdValidator(new String[]{"event","task"}));
        }
		return BPEventTaskContainer;    	
    }
    /***************************** 
    PANELS
    ******************************/
    public AETableContainer getPanelsContainer() throws Exception{
    	if (PanelsContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   PanelsContainer=new AETableContainer(PanelsTable,false,engine);
     	   PanelsContainer.setTableParentNewValue(companyId);
     	   PanelsContainer.addUniqueIndex(new String[]{"account","name"});
        }
		return PanelsContainer;    	
    }
    
    public AETableContainer getItemPanelsContainer() throws Exception{
    	if (ItemPanelsContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   ItemPanelsContainer=new AETableContainer(ItemPanelsTable,false,engine);
     	   ItemPanelsContainer.setTableParentNewValue(companyId);
     	   ItemPanelsContainer.addUniqueIndex(new String[]{"panels","statisticusesid"});
     	   ItemPanelsContainer.addValidator(new AENotNullIdValidator(new String[]{"panels","statisticusesid"}));
        }
		return ItemPanelsContainer;    	
    }

	@Override
	public AETableContainer getAETableContainer(String viewOrTableName)
			throws Exception {
		if (viewOrTableName==null) return null;
		if (viewOrTableName.equals("bppriority"))
			return getBPPriorityContainer();
		if (viewOrTableName.equals("bptasktype"))
			return getBPTaskTypeContainer();
		if (viewOrTableName.equals("bpevent"))
			return getBPEventContainer();
		if (viewOrTableName.equals("bpobject"))
			return getBPObjectContainer();
		if (viewOrTableName.equals("bptask"))
			return getBPTaskContainer();
		if (viewOrTableName.equals("bppost"))
			return getBPPostContainer();
		if (viewOrTableName.equals("bpdocument"))
			return getBPDocumentContainer();
		if (viewOrTableName.equals("bpeventpost"))
			return getBPEventPostContainer();
		if (viewOrTableName.equals("bpeventtask"))
			return getBPEventTaskContainer();
		if (viewOrTableName.equals("compacc"))
			return getCompAccContainer();
		if (viewOrTableName.equals("orgschema"))
			return getOrgschemaReadContainer();
		if (viewOrTableName.equals("documents"))
			return getDocumentContainer();
		if (viewOrTableName.equals("EventPostTask"))
			return views.getEventPostTaskContainer();
		return null;
	}
    /***************************** 
			STATISTIC DATA
     ******************************/
    public AETableContainer getStatisticDataContainer() throws Exception{
    	if (StatisticDataContainer==null)
        {
     	   if (companyId==null) throw new Exception("Company is not set");
     	   StatisticDataContainer=new AETableContainer(StatisticDataTable,false,engine);
     	   StatisticDataContainer.setTableParentNewValue(companyId);
     	   StatisticDataContainer.addValidator(new AENotNullIdValidator(new String[]{"statisticuses"}));
        }
		return StatisticDataContainer;    	
    }
    
    public void removeCompany(Object companyId,boolean forced) throws Exception
    {
    	setCompanyId(companyId);
    
    	if (forced)
    	{
    		getDocRightContainer().removeAllItems();
    		getDocRightContainer().commit();
    		getDocumentContainer().removeAllItems();
    		getDocumentContainer().commit();
    		getOrgschemaContainer().removeAllItems();
    		getOrgschemaContainer().commit();
    		getCompAccContainer().removeAllItems();
    		getCompAccContainer().commit();
    		getOrgLevelContainer().removeAllItems();
    		getOrgLevelContainer().commit();
    		getAPeriodPropsContainer().removeAllItems();
    		getAPeriodPropsContainer().commit();
    		getDictStatisticContainer().removeAllItems();
    		getDictStatisticContainer().commit();
    		getStatisticUsesContainer().removeAllItems();
    		getStatisticUsesContainer().commit();
    		getStatisticDataContainer().removeAllItems();
    		getStatisticDataContainer().commit();
    		getBPPriorityContainer().removeAllItems();
			getBPPriorityContainer().commit();
    		getBPTaskTypeContainer().removeAllItems();
            getBPTaskTypeContainer().commit();
    		getBPObjectContainer().removeAllItems();
            getBPObjectContainer().commit();
    		getBPTaskContainer().removeAllItems();
            getBPTaskContainer().commit();
    		getBPPostContainer().removeAllItems();
            getBPPostContainer().commit();
    		getBPDocumentContainer().removeAllItems();
            getBPDocumentContainer().commit();
    		getBPEventPostContainer().removeAllItems();
            getBPEventPostContainer().commit();
    		getBPEventTaskContainer().removeAllItems();
            getBPEventTaskContainer().commit();
    	}
    	else
    	{
    		if (getDocRightContainer().size()>0 
    		 || getDocumentContainer().size()>0
    		 || getOrgschemaContainer().size()>0
    		 || getCompAccContainer().size()>0
    		 || getOrgLevelContainer().size()>0
    		 || getAPeriodPropsContainer().size()>0
    		 || getDictStatisticContainer().size()>0
    		 || getStatisticUsesContainer().size()>0
    		 || getStatisticDataContainer().size()>0
			 || getBPPriorityContainer().size()>0
    		 || getBPTaskTypeContainer().size()>0
    		 || getBPObjectContainer().size()>0
    		 || getBPTaskContainer().size()>0
    		 || getBPPostContainer().size()>0
    		 || getBPDocumentContainer().size()>0
    		 || getBPEventPostContainer().size()>0
    		 || getBPEventTaskContainer().size()>0
    		 || getItemPanelsContainer().size()>0
    		 || getPanelsContainer().size()>0
    			)
    			throw new Exception("Company data are not empty!");
    	}
    	getCompContainer().removeItem(companyId);
    }
    public void refreshAll() throws Exception
    {
      	if (DocRightContainer!=null) DocRightContainer.refreshGeneral();
    	if (DocumentContainer!=null) DocumentContainer.refreshGeneral();
    	if (OrgschemaReadContainer!=null) OrgschemaReadContainer.refreshGeneral();
    	if (OrgschemaContainer!=null) OrgschemaContainer.refreshGeneral();
    	if (CompAccContainer!=null) CompAccContainer.refreshGeneral();
    	if (CompContainer!=null) CompContainer.refreshGeneral();
    	if (OrgLevelContainer!=null) OrgLevelContainer.refreshGeneral();
    	if (APeriodPropsContainer!=null) APeriodPropsContainer.refreshGeneral();
    	if (DictStatisticContainer!=null) DictStatisticContainer.refreshGeneral();
    	if (StatisticUsesContainer!=null) StatisticUsesContainer.refreshGeneral();
    	if (CurrentUserParamContainer!=null) CurrentUserParamContainer.refreshGeneral();
    	if (StatisticDataContainer!=null) StatisticDataContainer.refreshGeneral();
    	if (PanelsContainer!=null) PanelsContainer.refreshGeneral();
    	if (ItemPanelsContainer!=null) ItemPanelsContainer.refreshGeneral();
    }
}
